# Based on pandas cheatsheet

#%%
import pandas as pd

# construct DataFrame by dict with values of list
# every key-val pair is a column
pd.DataFrame(
    {"a" : [4, 5, 6], 
     "b" : [7, 8, 9], 
     "c" : [10, 11, 12]})
# default rowname and colnames are both like index: 0, 1, 2, 3...

# %%
# for dict
# provide rownames as index
pd.DataFrame(
    {"a" : [4, 5, 6], 
     "b" : [7, 8, 9], 
     "c" : [10, 11, 12]},
     index=['d1','d2','d3'])

# %%
# construct by nested list is ok
# every sublist is a row
pd.DataFrame(
     [[4,1], [5,2], [6,3]])

# %%
# for sublist
# provide rownames as index, colnames as columns
pd.DataFrame(
    [[4,1], [5,2], [6,3]],
     index=['d1','d2','d3'],
     columns=['col1','col2'])

# %%
# can create multi index (rownames) by tuples
df = pd.DataFrame(
    {"a" : [4 ,5, 6], 
     "b" : [7, 8, 9], 
     "c" : [10, 11, 12]}, 
     index = pd.MultiIndex.from_tuples(
         [('d', 1), ('d', 2), ('e', 2)],
          names=['n', 'v']))
df

# %%
# ----------------------------- test on real data ---------------------------- #
# read every text as csv, provide colnames
pd.read_csv('../None-requirements.txt', sep='==', names=['package', 'version'])

# %%
# read some big table as example
df = pd.read_csv('~/clues1.csv')
df

# %%
# use [] to filter rows
df[df.cg_cov == 'B']

# %%
# defualt head & tail n=5
# also, default preview is head + tail
df.head()
df.tail()

# %%
# drop duplicates on some column
df.drop_duplicates(subset='.cell')

# %%
# randomly sample on rows or fraction
df.sample(n = 6)
df.sample(frac=.01)

# %% 
# slice min & max
df1 = df.nlargest(1,'Age')
df2 = df.nsmallest(1,'Age')
# bind rows by concat
pd.concat([df1, df2])

# %%
df['cg_cov']
df.cg_cov
# subset multi-column
df[['cg_cov','ct_cov']]
# pd.filter can filter both (defaultly) column and row
df.filter(items=['cg_cov','ct_cov'])
df.filter(regex='cov')

# %%
# axis 1 means column, axis 0 means row
df.filter(items=[0,5], axis=0)

# %%
# concat on axis 1 (bind_col)
df1 = df['cg_cov']
df2 = df['ind_cov']
pd.concat([df1, df2], axis=1)

# %%
# drop columns and rows
df.drop(columns=['...1'])
df.drop(labels=[2])

# %%
# sort by some column's value
df.sort_values('Age')

# %%
# rename column or row by supply dict of old:new pairs
df.rename(columns={'...1':'old_barcode', '.cell':'stripped_barcode'})
# chain method by . or []; wrap in () if multi-line
(df.rename(columns={'...1':'old_barcode', '.cell':'stripped_barcode'})
 .filter(items=['old_barcode','stripped_barcode']))

# %%
# query rows by boolean combination
df.query('Age > 60 or Age < 30')

# %%
# melt is like pivot_longer
df.head().melt(id_vars=['ind_cov'],value_vars=['cg_cov'])

# %%
# pivot is like pivot_wider?
df[df.cg_cov == 'B'].filter(items=['cg_cov','.cell']).pivot(columns='cg_cov',values='.cell')

# %%
# df.iloc[] select specific rows and/or cols by index
# df[index] can only select rows
# keep in mind that index begin at 0
df[1:10] ; df.iloc[1:10]
df.iloc[:, 0:2]
df.iloc[:5, :5]

# %%
# .loc[] select specific rows and/or cols by name or expression
# df['name'] can only select columns
# df[<expr>] can only subset rows
df.loc[:, 'batch_cov':'Status']
df.loc[df.cg_cov == 'T4', 'batch_cov':'Status']

# %%
# .iat[] select single value by index
df.iat[1, 1]
# .at[] select single value by name
df.at[0, 'batch_cov']

# %%
# .value_counts() count unique appearance
print(df.batch_cov.value_counts())
# .nunique() count number of unique entries
df.batch_cov.nunique()

# %%
# #rows of df
len(df)
# dims of df
df.shape

# %%
# show basic stat of all numeric cols
df.describe()
# compute stat on single col
df.Age.mean()

# %%
# drop rows with any NA
df.dropna()
# or specify cols to examine
df.dropna(subset=['ct_cov'])

# %%
# fill na with value
df.fillna('unknown')

# %%
# .assign with anonymous func (lambda) to mutate new column
df.assign(age_above_60 = lambda df: df.Age > 60)
# or use df[new_col] to create a new col
# df.new_col will raise warning
df['age_above_60'] = df.Age > 60

# %%
# pd.qcut generate new category array on provided col's quantile
pd.qcut(df.Age, 2, labels=False)
# use labels=False to generate simple integer array
# default will generate interval dtype

# %%
# summarize along cols (axis=1) or along rows (axis=0)
print(pd.DataFrame([[1,2],[3,4],[5,6]]))
print('\nsummarize along cols:\n',pd.DataFrame([[1,2],[3,4],[5,6]]).min(axis=0))
print('\nsummarize along rows:\n',pd.DataFrame([[1,2],[3,4],[5,6]]).min(axis=1))

# %%
# clip too high or too low value
pd.DataFrame([[1,2],[3,4],[5,6]]).clip(2,5)

# %%
celltype = pd.DataFrame({
    'cg_cov' : ['T4','T8','B','cM','NK','ncM','Prolif','cDC','pDC','PB'],
    'cell_type' : ['CD4T', 'CD8T', 'B_cell', 'classical_monocyte', 'NK_cell',
                   'non-classical_monocyte', 'proliferating_T&NK',
                   'convential_DC', 'plasmacytoid_DC', 'plasma_blast']
})
print(celltype)
# .merge(how='left') left join
df.merge(celltype, how='left')

# %%
# .isin() match in group 
df[df.cg_cov.isin(['B','PB'])]

# %%
# group by batch and aggregate oldest sample in every batch
df.groupby('batch_cov').Age.agg('max')

# %%
# simple histogram
df['Age'].plot.hist()

# %%
# simple scatter
df.plot.scatter('Age','Status')

# %%
